Week Of: 2022-09-18
22/09/2022

This can be good solution in case you want to aggregate data on the application side.
This view is based on "live" data and not on synchronized.

CREATE OR REPLACE FORCE EDITIONABLE VIEW "APX_ADMIN"."OP_USERS_V" 
 ( "MIS_ZEHUT", "PRATI", "PK_GGC", "USERNAME", "NODE", "MISHPACHA"
  )  AS 
  SELECT SVC.MIS_ZEHUT , SVC.PRATI, SVC.PK_GGC, SVC.USERNAME, SVC.NODE, SVC.MISHPACHA
  FROM TABLE(WWV_FLOW_T_CLOB(APEX_WEB_SERVICE.MAKE_REST_REQUEST(P_URL         => 'https://apex-ora-devtest.openu.ac.il:8443/oradeva/auth/v1/users',
                                                                P_HTTP_METHOD => 'GET'))) APX,
       JSON_TABLE(APX.COLUMN_VALUE FORMAT JSON,
                  '$."items"[*]'
                  COLUMNS("NODE" VARCHAR2(4000) PATH '$."node"',
                          "PRATI" VARCHAR2(4000) PATH '$."prati"',
                          "PK_GGC" VARCHAR2(4000) PATH '$."pk_ggc"',
                          "USERNAME" VARCHAR2(4000) PATH '$."username"',
                          "MIS_ZEHUT" VARCHAR2(4000) PATH '$."mis_zehut"',
                          "MISHPACHA" VARCHAR2(4000) PATH '$."mishpacha"')) SVC